--- title: ConnectionsController summary: Three GET endpoints that return a composed, credential-bearing connection string for an Oracle, SQL Server or PostgreSQL target. status: draft owner: Architecture last_reviewed: 2026-10-09 --- # ConnectionsController `ConnectionsController` is the retrieval surface of the service. Each endpoint resolves a caller-named server, instance and user into a connection string by combining an Environment API template with a secure credential, validates Oracle and SQL Server strings with a connectivity probe, and caches the result in process memory. The returned string contains credentials and is flagged CRITICAL (see [Authentication and authorization](../authentication-authorization.md#security-findings)). Each request is audit-logged ([BR-DBC-022](../business-rules.md#br-dbc-022-retrieval-requests-are-audit-logged)). Callers are not determined from source**. | Field | Value | | --- | --- | | **Controller** | `ConnectionsController` | | **Base** | `ControllerBase` | | **`[ApiController]`** | Applied | | **Route template** | `v1/Connections` (the controller-name token resolves to `Connections`) | | **API version** | Literal route prefix `v1` (no API versioning) | | **Source** | `DBConnectionAPI/DBConnectionAPI/Controllers/ConnectionsController.cs` | | **Controller-level authorization** | `[SecurityApiAuthorize("DBConnAPIRetrieveResource")]` | | **Error handling** | No local mechanism. `ConnectionManagerException : HttpStatusCodeException` and any unhandled exception flow to opaque package middleware (`DynamicErrorHandler`) † | | **Endpoints** | 3 | --- ## GET v1 Connections SQL server instance username Returns a validated SQL Server connection string for the named server, instance and user. | Field | Value | | --- | --- | | **HTTP verb** | `GET` | | **Resolved route** | `/v1/Connections/SQL/{server}/{instance}/{username}` | | **Route composition** | Class-level `[Route]` template + `[HttpGet("SQL/{server}/{instance}/{username}")]`, with the controller-name token resolved to `Connections` | | **Action** | `GetSqlConnectionString` | | **Return type** | `ActionResult` | | **Declared responses** | None | | **Authorization** | [`DBConnAPIRetrieveResource`](../authentication-authorization.md#authorization-enforcement) | | **Idempotent** | Yes (same result for the same inputs; the first call per key also populates the cache) | | **Operation** | OP-01 — see [Operations and processes](../operations-processes.md) | ### Parameters (SQL Server) | Name | In | Type | Required | Default | Binding | Purpose | | --- | --- | --- | --- | --- | --- | --- | | `server` | Route | `string` | Yes | — | Inferred by `[ApiController]` (name appears in the route template) | SQL Server host substituted into the template and keys | | `instance` | Route | `string` | Yes | — | Inferred by `[ApiController]` | Instance name substituted into the template and keys | | `username` | Route | `string` | Yes | — | Inferred by `[ApiController]` | User whose credential is retrieved | No validation is applied to these values: they flow unvalidated into cache keys, secure-setting key names and templates. ### Response (SQL Server) | Field | Value | | --- | --- | | **DTO** | None — `string` | | **Media type** | Framework default for a `string` result, normally `text/plain`*; the formatter in force is decided inside the opaque base** | | Property | Type | Nullable | Purpose | | --- | --- | --- | --- | | (body) | `string` | No | Composed SQL Server connection string containing the credential | ### Status codes (SQL Server) | Status | Condition | Body | | --- | --- | --- | | `200 OK` | Cache hit, or the credential was retrieved, composed and the probe succeeded ([BR-DBC-001](../business-rules.md#br-dbc-001-serve-from-the-cache-when-the-key-is-present), [BR-DBC-018](../business-rules.md#br-dbc-018-probe-validity-definition)) | Connection string | | Error status | Trigger | Produced by | Body | | --- | --- | --- | --- | | `404 Not Found` | The secure setting does not exist ([BR-DBC-009](../business-rules.md#br-dbc-009-missing-secure-setting-returns-404)) | `ConnectionManagerException(404)` → `DynamicErrorHandler` † | Message naming the setting group, key and track; envelope not visible** | | `400 Bad Request` | A final failure mapped to an alias error ([BR-DBC-015](../business-rules.md#br-dbc-015-tns-alias-errors-map-to-400)); possible for SQL Server only by inference* | `ConnectionManagerException(400)` → `DynamicErrorHandler` † | Message; envelope not visible** | | `500 Internal Server Error` | Probe failed after retries, invalid credentials, probe returned false, or generic failure ([BR-DBC-014](../business-rules.md#br-dbc-014-a-false-probe-result-is-a-failure), [BR-DBC-016](../business-rules.md#br-dbc-016-other-final-failures-map-to-500)) | `ConnectionManagerException(500)` → `DynamicErrorHandler` † | Message that can include the cache key, setting key, track and raw provider message | | `500 Internal Server Error`** | Unhandled exception: stored credential has no pipe delimiter ([BR-DBC-010](../business-rules.md#br-dbc-010-credential-split-at-the-first-pipe%2C-unguarded)), or a template setting is missing | Opaque package middleware** | Not visible in source** | | `401 Unauthorized` / `403 Forbidden` | Caller lacks `DBConnAPIRetrieveResource` ([BR-DBC-025](../business-rules.md#br-dbc-025-resource-authorization-on-every-endpoint)); exact status not stated** | `[SecurityApiAuthorize]` via the opaque authorization pipeline † | Not visible in source** | All of 400, 404 and 500 are undeclared in `[ProducesResponseType]`. ### Authorization (SQL Server) Requires resource `DBConnAPIRetrieveResource` (controller level). See [Authentication and authorization](../authentication-authorization.md). ### Side effects and idempotency (SQL Server) | Aspect | Finding | | --- | --- | | Records written | None in any database table; one entry is added to the in-memory connection cache on a validated cache miss ([BR-DBC-003](../business-rules.md#br-dbc-003-guarded-cache-insert%2C-first-writer-wins)) | | Downstream calls | SAIF Environment API (`IEnvironmentClient`); a `SELECT 1` probe against the caller-named SQL Server on a cache miss ([BR-DBC-018](../business-rules.md#br-dbc-018-probe-validity-definition)), up to 4 attempts ([BR-DBC-012](../business-rules.md#br-dbc-012-four-probe-attempts-with-2%2C-3-and-5-second-backoff)) | | Files or messages produced | Info audit log entry per request ([BR-DBC-022](../business-rules.md#br-dbc-022-retrieval-requests-are-audit-logged)) | | Repeat-call behavior | Reads the template again, then served from the cache with no secure read and no probe; entries never expire until the process restarts ([BR-DBC-004](../business-rules.md#br-dbc-004-cache-entries-never-expire)) | ### Example (SQL Server) ```http GET /v1/Connections/SQL/// HTTP/1.1 Host: Authorization: ****** ``` ```http HTTP/1.1 200 OK > ``` --- ## GET v1 Connections PostgreSQL instance username Returns a composed PostgreSQL connection string for the named instance and user. The string is not probed. | Field | Value | | --- | --- | | **HTTP verb** | `GET` | | **Resolved route** | `/v1/Connections/PostgreSQL/{instance}/{username}` | | **Route composition** | Class-level `[Route]` template + `[HttpGet("PostgreSQL/{instance}/{username}")]`, with the controller-name token resolved to `Connections` | | **Action** | `GetPostgreSqlConnectionString` | | **Return type** | `ActionResult` | | **Declared responses** | None | | **Authorization** | [`DBConnAPIRetrieveResource`](../authentication-authorization.md#authorization-enforcement) | | **Idempotent** | Yes (same result for the same inputs; first call populates the cache) | | **Operation** | OP-02 — see [Operations and processes](../operations-processes.md) | ### Parameters (PostgreSQL) | Name | In | Type | Required | Default | Binding | Purpose | | --- | --- | --- | --- | --- | --- | --- | | `instance` | Route | `string` | Yes | — | Inferred by `[ApiController]` | Instance substituted into the template and keys | | `username` | Route | `string` | Yes | — | Inferred by `[ApiController]` | User whose credential is retrieved | | `serverName` | Query | `string` | No | `{SERVER}` placeholder when blank | `[FromQuery]` | Server substituted into the template; a null or blank value becomes the literal placeholder `{SERVER}` ([BR-DBC-021](../business-rules.md#br-dbc-021-blank-postgresql-server-becomes-a-placeholder)) | The PostgreSQL credential key has no server component ([BR-DBC-006](../business-rules.md#br-dbc-006-postgresql-credentials-ignore-the-server)). ### Response (PostgreSQL) | Field | Value | | --- | --- | | **DTO** | None — `string` | | **Media type** | Framework default for a `string` result, normally `text/plain`*; decided by the opaque base** | | Property | Type | Nullable | Purpose | | --- | --- | --- | --- | | (body) | `string` | No | Composed PostgreSQL connection string containing the credential; may retain the `{SERVER}` placeholder | ### Status codes (PostgreSQL) | Status | Condition | Body | | --- | --- | --- | | `200 OK` | Cache hit, or the credential was retrieved and composed ([BR-DBC-020](../business-rules.md#br-dbc-020-postgresql-strings-are-never-probed)) | Connection string | | Error status | Trigger | Produced by | Body | | --- | --- | --- | --- | | `404 Not Found` | The secure setting does not exist ([BR-DBC-009](../business-rules.md#br-dbc-009-missing-secure-setting-returns-404)) | `ConnectionManagerException(404)` → `DynamicErrorHandler` † | Message naming the group, key and track; envelope not visible** | | `500 Internal Server Error`** | Unhandled exception: no pipe delimiter in the stored credential ([BR-DBC-010](../business-rules.md#br-dbc-010-credential-split-at-the-first-pipe%2C-unguarded)), or a missing template setting | Opaque package middleware** | Not visible in source** | | `401 Unauthorized` / `403 Forbidden` | Caller lacks `DBConnAPIRetrieveResource` ([BR-DBC-025](../business-rules.md#br-dbc-025-resource-authorization-on-every-endpoint)); exact status not stated** | `[SecurityApiAuthorize]` via the opaque authorization pipeline † | Not visible in source** | No 400 from a connection test can occur here because PostgreSQL strings are never probed. Every non-200 status is undeclared. ### Authorization (PostgreSQL) Requires resource `DBConnAPIRetrieveResource` (controller level). See [Authentication and authorization](../authentication-authorization.md). ### Side effects and idempotency (PostgreSQL) | Aspect | Finding | | --- | --- | | Records written | None in any database table; one in-memory cache entry per new key ([BR-DBC-003](../business-rules.md#br-dbc-003-guarded-cache-insert%2C-first-writer-wins)) | | Downstream calls | SAIF Environment API (`IEnvironmentClient`); no database probe | | Files or messages produced | Info audit log entry per request ([BR-DBC-022](../business-rules.md#br-dbc-022-retrieval-requests-are-audit-logged)) | | Repeat-call behavior | Reads the template again, then served from the cache without a secure read; the cache key includes the server, so different `serverName` values create separate entries ([BR-DBC-002](../business-rules.md#br-dbc-002-cache-key-format)) | ### Example (PostgreSQL) ```http GET /v1/Connections/PostgreSQL//?serverName= HTTP/1.1 Host: Authorization: ****** ``` ```http HTTP/1.1 200 OK > ``` --- ## GET v1 Connections ORA instance username Returns a validated Oracle connection string for the named instance and user. | Field | Value | | --- | --- | | **HTTP verb** | `GET` | | **Resolved route** | `/v1/Connections/ORA/{instance}/{username}` | | **Route composition** | Class-level `[Route]` template + `[HttpGet("ORA/{instance}/{username}")]`, with the controller-name token resolved to `Connections` | | **Action** | `GetOraConnectionString` | | **Return type** | `ActionResult` | | **Declared responses** | None | | **Authorization** | [`DBConnAPIRetrieveResource`](../authentication-authorization.md#authorization-enforcement) | | **Idempotent** | Yes (same result for the same inputs; first call populates the cache) | | **Operation** | OP-03 — see [Operations and processes](../operations-processes.md) | ### Parameters (Oracle) | Name | In | Type | Required | Default | Binding | Purpose | | --- | --- | --- | --- | --- | --- | --- | | `instance` | Route | `string` | Yes | — | Inferred by `[ApiController]` | Oracle instance or TNS alias substituted into the template | | `username` | Route | `string` | Yes | — | Inferred by `[ApiController]` | User whose credential is retrieved | ### Response (Oracle) | Field | Value | | --- | --- | | **DTO** | None — `string` | | **Media type** | Framework default for a `string` result, normally `text/plain`*; decided by the opaque base** | | Property | Type | Nullable | Purpose | | --- | --- | --- | --- | | (body) | `string` | No | Composed Oracle connection string containing the credential; a legacy token may remain unsubstituted in the returned value ([BR-DBC-019](../business-rules.md#br-dbc-019-legacy-token-decoded-for-the-probe-only)) | ### Status codes (Oracle) | Status | Condition | Body | | --- | --- | --- | | `200 OK` | Cache hit, or the credential was retrieved, composed and the probe succeeded ([BR-DBC-001](../business-rules.md#br-dbc-001-serve-from-the-cache-when-the-key-is-present), [BR-DBC-018](../business-rules.md#br-dbc-018-probe-validity-definition)) | Connection string | | Error status | Trigger | Produced by | Body | | --- | --- | --- | --- | | `404 Not Found` | The secure setting does not exist ([BR-DBC-009](../business-rules.md#br-dbc-009-missing-secure-setting-returns-404)) | `ConnectionManagerException(404)` → `DynamicErrorHandler` † | Message naming the group, key and track; envelope not visible** | | `400 Bad Request` | TNS alias not found ([BR-DBC-015](../business-rules.md#br-dbc-015-tns-alias-errors-map-to-400)) | `ConnectionManagerException(400)` → `DynamicErrorHandler` † | Message citing the alias and track | | `500 Internal Server Error` | Database unavailable, cursor limit, invalid credentials, network interruption, probe returned false, or generic failure ([BR-DBC-014](../business-rules.md#br-dbc-014-a-false-probe-result-is-a-failure), [BR-DBC-016](../business-rules.md#br-dbc-016-other-final-failures-map-to-500), [BR-DBC-017](../business-rules.md#br-dbc-017-an-existing-typed-exception-keeps-its-status)) | `ConnectionManagerException(500)` → `DynamicErrorHandler` † | Message that can include the key, setting name, track and raw provider message | | `500 Internal Server Error`** | Unhandled exception: no pipe delimiter in the stored credential ([BR-DBC-010](../business-rules.md#br-dbc-010-credential-split-at-the-first-pipe%2C-unguarded)), or a missing template setting | Opaque package middleware** | Not visible in source** | | `401 Unauthorized` / `403 Forbidden` | Caller lacks `DBConnAPIRetrieveResource` ([BR-DBC-025](../business-rules.md#br-dbc-025-resource-authorization-on-every-endpoint)); exact status not stated** | `[SecurityApiAuthorize]` via the opaque authorization pipeline † | Not visible in source** | All of 400, 404 and 500 are undeclared in `[ProducesResponseType]`. ### Authorization (Oracle) Requires resource `DBConnAPIRetrieveResource` (controller level). See [Authentication and authorization](../authentication-authorization.md). ### Side effects and idempotency (Oracle) | Aspect | Finding | | --- | --- | | Records written | None in any database table; one in-memory cache entry per validated new key ([BR-DBC-003](../business-rules.md#br-dbc-003-guarded-cache-insert%2C-first-writer-wins)) | | Downstream calls | SAIF Environment API (`IEnvironmentClient`); a `SELECT 1 FROM DUAL` probe against the caller-named Oracle target on a cache miss, up to 4 attempts with 2 s, 3 s and 5 s waits ([BR-DBC-012](../business-rules.md#br-dbc-012-four-probe-attempts-with-2%2C-3-and-5-second-backoff), [BR-DBC-013](../business-rules.md#br-dbc-013-permanent-errors-stop-retrying)) | | Files or messages produced | Info audit log entry per request ([BR-DBC-022](../business-rules.md#br-dbc-022-retrieval-requests-are-audit-logged)) | | Repeat-call behavior | Reads the template again, then served from the cache with no secure read and no probe; keys are case-sensitive, so a request differing only in case probes again ([BR-DBC-002](../business-rules.md#br-dbc-002-cache-key-format)) | ### Example (Oracle) ```http GET /v1/Connections/ORA// HTTP/1.1 Host: Authorization: ****** ``` ```http HTTP/1.1 200 OK > ``` --- ## Related documentation - [API index](../index.md) - [Overview](../overview.md) - [Architecture](../architecture.md) - [Hosting pipeline](../hosting-pipeline.md) - [Endpoints](../endpoints.md) - [Authentication and authorization](../authentication-authorization.md) - [Dependencies](../dependencies.md) - [Configuration](../configuration.md) - [Operations and processes](../operations-processes.md) - [Business rules](../business-rules.md) - [Data model](../data-model.md) - [Operational runbook](../operational-runbook.md) - [Tech debt](../tech-debt.md) > **Note:** Statements marked with `†` are derived from dependency library > documentation via the Legends CLI. Statements marked with `*` were inferred > from code structure or contextual evidence. Statements marked with `**` could > not be confirmed; treat as unknown until verified. ## Sources - DBConnectionAPI/DBConnectionAPI/Controllers/ConnectionsController.cs - DBConnectionAPI/DBConnectionAPI/Services/ConnectionManager.cs - DBConnectionAPI/DBConnectionAPI/Services/IConnectionManager.cs - DBConnectionAPI/DBConnectionAPI/Exceptions/ConnectionManagerException.cs - DBConnectionAPI/DBConnectionAPI/EnvironmentKeys.cs